package com.hengpeng.msls.service.assign.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;

import com.hengpeng.common.persist.query.DynamicQuery;
import com.hengpeng.common.persist.query.Projections;
import com.hengpeng.common.persist.query.constants.QueryConstants.QueryType;
import com.hengpeng.common.util.DateTimeUtil;
import com.hengpeng.itfinbase.persist.BorrowInfo;
import com.hengpeng.itfinbase.persist.CreditAssign;
import com.hengpeng.itfinbase.persist.CreditAssign.AssignStatus;
import com.hengpeng.itfinbase.persist.InvestInfo;
import com.hengpeng.itfinbase.persist.ReceivePlan;
import com.hengpeng.msls.service.ServiceFactory;
import com.hengpeng.msls.service.assign.AssignService;
import com.hengpeng.msls.vo.assign.CreditAssignVo;

@Lazy
@Service
public class AssignServiceImpl implements AssignService
{
	@Autowired
	private ServiceFactory serviceFactory;

	@SuppressWarnings("rawtypes")
	@Override
	public List<CreditAssignVo> queryCanCreditAssignList(Long nowOwnUserId)
	{
		/**
		 *  select t.id,t.borrowName,t.borrowInfoId,t.borrowRate,t.ysqs,t.dsbj from 
			(
				select i.id,i.borrowName,i.borrowInfoId,b.borrowRate,i.investUserId,b.loanTime,
				sum(case r.status when 'RECEIVED' then 1 else 0 end) as ysqs,
				sum(case r.status when 'WAITREC' then 1 else 0 end) as wsqs,
				sum(case r.status when 'WAITREC' then r.recBaseMoney else 0 end) as dsbj,
				sum(case date_format(r.arrivalTime,'%Y-%m-%d') when date_format(now(),'%Y-%m-%d') then 1 else 0 end) as dtbnyhk 
				from InvestInfo i , BorrowInfo b , ReceivePlan r 
				where i.borrowInfoId = b.id and i.id = r.investmentInfoId and b.id = r.borrowInfoId 
				and i.nowOwnUserId = ? 
				group by i.id,i.borrowName,i.borrowInfoId,b.borrowRate,i.investUserId,b.loanTime
			
			)t where t.wsqs > 0 and t.loanTime < ? and t.dtbnyhk = 0
		 * 
		 */
		DynamicQuery queryinner = new DynamicQuery(InvestInfo.class, "i");
		queryinner.createAlias(BorrowInfo.class, "b");
		queryinner.createAlias(ReceivePlan.class, "r");
		queryinner.eqProperty("i.borrowInfoId", "b.id");
		queryinner.eqProperty("i.id", "r.investmentInfoId");
		queryinner.eqProperty("b.id", "r.borrowInfoId");
		
		queryinner.addProjection(Projections.property("i.id"));
		queryinner.addProjection(Projections.property("i.borrowName"));
		queryinner.addProjection(Projections.property("i.borrowInfoId"));
		queryinner.addProjection(Projections.property("b.borrowRate"));
		queryinner.addProjection(Projections.property("i.investUserId"));
		queryinner.addProjection(Projections.property("b.loanTime"));
		queryinner.addProjection(Projections.sum("case r.status when 'RECEIVED' then 1 else 0 end","ysqs"));//已回款期数
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then 1 else 0 end","wsqs"));//未收期数
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then r.recBaseMoney else 0 end","dsbj"));//待收本金
		queryinner.addProjection(Projections.sum("case date_format(r.arrivalTime,'%Y-%m-%d') when date_format(now(),'%Y-%m-%d') then 1 else 0 end", "dtbnyhk"));//当天是否有回款
		
		queryinner.eq("i.investUserId", nowOwnUserId);//根据当前债权人来查询
		
		DynamicQuery notExistsQuery = new DynamicQuery(CreditAssign.class, "c");
		notExistsQuery.addProjection(Projections.property("1"));
		notExistsQuery.eqProperty("i.id", "c.investInfoId");
		notExistsQuery.inArray("c.status", new AssignStatus[]{AssignStatus.PUBLISHED,AssignStatus.ASSIGNING,AssignStatus.ASSIGNSUCCESS});
		queryinner.notExists(notExistsQuery);
		
		queryinner.addGourp("i.id");
		queryinner.addGourp("i.borrowName");
		queryinner.addGourp("i.borrowInfoId");
		queryinner.addGourp("b.borrowRate");
		queryinner.addGourp("i.investUserId");
		queryinner.addGourp("b.loanTime");
		
		DynamicQuery queryout = new DynamicQuery(queryinner, "t");
		queryout.addProjection(Projections.property("t.id"));//0
		queryout.addProjection(Projections.property("t.borrowName"));//1
		queryout.addProjection(Projections.property("t.borrowInfoId"));//2
		queryout.addProjection(Projections.property("t.borrowRate"));//3
		queryout.addProjection(Projections.property("t.wsqs"));//4
		queryout.addProjection(Projections.property("t.dsbj"));//5
		queryout.gt("t.wsqs", 0);//投标标的必须是回款中
		queryout.lt("t.loanTime", DateTimeUtil.addDate(new Date(), -30));//满标放款30天后才能转让
		queryout.eq("t.dtbnyhk", 0);//当天不能有回款记录
		queryout.setQueryType(QueryType.JDBC);
		System.out.println(queryout.getSqlResult().getSql());
		List l = serviceFactory.getProtFactory().findObject(null, queryout);
		List<CreditAssignVo> dataList = new ArrayList<CreditAssignVo>();
		if (l != null && l.size() > 0) {
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[])l.get(i);
				CreditAssignVo vo = new CreditAssignVo();
				vo.setInvestInfoId(Long.parseLong(String.valueOf(obj[0])));
				vo.setBorrowName(String.valueOf(obj[1]));
				vo.setBorrowInfoId(Long.parseLong(String.valueOf(obj[2])));
				vo.setBorrowRate(Double.parseDouble(String.valueOf(obj[3])));
				vo.setSellMoney(Long.parseLong(String.valueOf(obj[5])));
				vo.setLeftPlanNum(Long.parseLong(String.valueOf(obj[4])));
				dataList.add(vo);
			}
		}
		return dataList;
	}
	
	@SuppressWarnings("rawtypes")
	@Override
	public List<CreditAssignVo> queryAssigningList(Long investUserId)
	{
		DynamicQuery queryinner = new DynamicQuery(InvestInfo.class, "i");
		queryinner.createAlias(CreditAssign.class, "c");
		queryinner.createAlias(ReceivePlan.class, "r");
		queryinner.eqProperty("i.id", "r.investmentInfoId");
		queryinner.eqProperty("i.id", "c.investInfoId ");
		
		
		queryinner.addProjection(Projections.property("c.borrowInfoId"));//0
		queryinner.addProjection(Projections.property("c.borrowName"));//1
		queryinner.addProjection(Projections.property("c.borrowRate"));//2
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then 1 else 0 end"));//3剩余期数
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then r.recBaseMoney else 0 end"));//4待收本金
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then r.recMoney else 0 end"));//5待收本息
		
		queryinner.eq("c.sellUserId", investUserId);//根据当前债权人来查询
		
		queryinner.addGourp("c.borrowInfoId");
		queryinner.addGourp("c.borrowName");
		queryinner.addGourp("c.borrowRate");
		
		queryinner.setQueryType(QueryType.JDBC);
		System.out.println(queryinner.getSqlResult().getSql());
		List l = serviceFactory.getProtFactory().findObject(null, queryinner);
		List<CreditAssignVo> dataList = new ArrayList<CreditAssignVo>();
		if (l != null && l.size() > 0) {
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[])l.get(i);
				CreditAssignVo vo = new CreditAssignVo();
				vo.setBorrowName(String.valueOf(obj[1]));
				vo.setBorrowInfoId(Long.parseLong(String.valueOf(obj[0])));
				vo.setBorrowRate(Double.parseDouble(String.valueOf(obj[2])));
				vo.setSellMoney(Long.parseLong(String.valueOf(obj[4])));
				vo.setLeftPlanNum(Long.parseLong(String.valueOf(obj[3])));
				vo.setSellMoney(Long.parseLong(String.valueOf(obj[5])));
				dataList.add(vo);
			}
		}
		return dataList;
	}

	@Override
	public List<CreditAssign> queryAssignedList(Long investUserId)
	{
		DynamicQuery queryinner = new DynamicQuery(CreditAssign.class, "c");
		queryinner.eq("c.status", CreditAssign.AssignStatus.ASSIGNSUCCESS);
		queryinner.eq("c.sellUserId", investUserId);
		
		queryinner.setQueryType(QueryType.JDBC);
		System.out.println(queryinner.getSqlResult().getSql());
		return serviceFactory.getProtFactory().queryList(queryinner,CreditAssign.class);
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List<CreditAssignVo> queryBuyedList(Long buyUserId)
	{
		DynamicQuery queryinner = new DynamicQuery(InvestInfo.class, "i");
		queryinner.createAlias(CreditAssign.class, "c");
		queryinner.createAlias(ReceivePlan.class, "r");
		queryinner.eqProperty("i.id", "r.investmentInfoId");
		queryinner.eqProperty("i.id", "c.investInfoId ");
		
		queryinner.addProjection(Projections.property("c.borrowInfoId"));//0
		queryinner.addProjection(Projections.property("c.borrowName"));//1
		queryinner.addProjection(Projections.property("c.borrowRate"));//2
		queryinner.addProjection(Projections.property("c.sellMoney"));//3
		queryinner.addProjection(Projections.property("c.leftPlanNum"));//4
		queryinner.addProjection(Projections.property("c.buyMoney"));//5
		queryinner.addProjection(Projections.property("c.assignSuccessTime"));//6
		queryinner.addProjection(Projections.sum("case r.status when 'WAITREC' then r.recMoney else 0 end","dsbx"));//7待收本息
		
		queryinner.eq("c.status", CreditAssign.AssignStatus.ASSIGNSUCCESS);
		queryinner.eq("c.buyUserId", buyUserId);
		
		queryinner.addGourp("c.borrowInfoId");
		queryinner.addGourp("c.borrowName");
		queryinner.addGourp("c.borrowRate");
		queryinner.addGourp("c.sellMoney");
		queryinner.addGourp("c.leftPlanNum");
		queryinner.addGourp("c.buyMoney");
		queryinner.addGourp("c.assignSuccessTime");
		
		queryinner.setQueryType(QueryType.JDBC);
		System.out.println(queryinner.getSqlResult().getSql());
		List l = serviceFactory.getProtFactory().findObject(null, queryinner);
		List<CreditAssignVo> dataList = new ArrayList<CreditAssignVo>();
		if (l != null && l.size() > 0) {
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[])l.get(i);
				CreditAssignVo vo = new CreditAssignVo();
				vo.setBorrowName(String.valueOf(obj[1]));
				vo.setBorrowInfoId(Long.parseLong(String.valueOf(obj[0])));
				vo.setBorrowRate(Double.parseDouble(String.valueOf(obj[2])));
				vo.setSellMoney(Long.parseLong(String.valueOf(obj[3])));
				vo.setLeftPlanNum(Long.parseLong(String.valueOf(obj[4])));
				vo.setBuyMoney(Long.parseLong(String.valueOf(obj[5])));
				vo.setAssignSuccessTime(DateTimeUtil.formatToDate(String.valueOf(obj[6]), DateTimeUtil.DATE_FORMAT_YYYYMMDD));
				dataList.add(vo);
			}
		}
		return dataList;
	}

	@SuppressWarnings("rawtypes")
	@Override
	public CreditAssign getCurrentAssignInfo(Long assignId)
	{
		CreditAssign creditAssign = new CreditAssign();
		DynamicQuery query = new DynamicQuery(InvestInfo.class, "i");
		query.createAlias(ReceivePlan.class, "r");
		query.createAlias(CreditAssign.class, "c");
		query.eqProperty("i.id", "r.investmentInfoId");
		query.eqProperty("c.investInfoId", "i.id");
		
		query.addProjection(Projections.property("i.id"));//0
		query.addProjection(Projections.property("i.borrowName"));//1
		query.addProjection(Projections.property("i.borrowInfoId"));//2
		query.addProjection(Projections.property("c.sellUserId"));//3转让人
		query.addProjection(Projections.property("c.sellTrustAccountId"));//4转让人托管账户
		query.addProjection(Projections.property("i.investMoney"));//5投标金额
		query.addProjection(Projections.sum("case r.status when 'WAITREC' then 1 else 0 end"));//6未收期数
		query.addProjection(Projections.sum("case r.status when 'RECEIVED' then r.recMoney else 0 end"));//7已收本息
		
		query.eq("c.id", assignId);
		query.addGourp("i.id");
		query.addGourp("i.borrowName");
		query.addGourp("i.borrowInfoId");
		query.addGourp("c.sellUserId");
		query.addGourp("c.sellTrustAccountId");
		query.addGourp("i.investMoney");
		System.out.println(query.getSqlResult().getSql());
		List list = serviceFactory.getProtFactory().findObject(null, query);
		if (list != null && list.size() > 0) {
			Object[] obj = (Object[])list.get(0);
			creditAssign.setInvestInfoId(Long.parseLong(String.valueOf(obj[0])));
			creditAssign.setBorrowName(String.valueOf(obj[1]));
			creditAssign.setBorrowInfoId(Long.parseLong(String.valueOf(obj[2])));
			creditAssign.setSellMoney(Long.parseLong(String.valueOf(obj[5])) - Long.parseLong(String.valueOf(obj[7])));//转让金额
			creditAssign.setLeftPlanNum(Long.parseLong(String.valueOf(obj[6])));
			creditAssign.setSellUserId(Long.parseLong(String.valueOf(obj[3])));
			creditAssign.setSellTrustAccountId(String.valueOf(obj[4]));
			creditAssign.setRepaymentedMoney(Long.parseLong(String.valueOf(obj[7])));
		}
		return creditAssign;
	}
	@Override
	public List<CreditAssign> queryList(AssignStatus[] status)
	{
		DynamicQuery query = new DynamicQuery(CreditAssign.class);
		if (status != null && status.length > 0)
		{
			query.inArray("status", status);
		}
		query.desc("case status when 'PUBLISHED' then 3 " + "when 'ASSIGNING' then 2 " + "when 'ASSIGNSUCCESS' then 1 else 0 end  ");
		query.desc("createTime");
		query.setQueryType(QueryType.JDBC);
		List<CreditAssign> result = serviceFactory.getProtFactory().queryList(query,CreditAssign.class);
		return result;
	}
	
	
	
}
